﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class DangKyDAO
    {
        #region SelectALLDangKy
        public static List<DangKyDTO> SelectALLDangKy()
        {
            List<DangKyDTO> list = new List<DangKyDTO>();
            try
            {
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectALLDangKy");
                foreach (DataRow dr in dt.Rows)
                {
                    DangKyDTO dk = new DangKyDTO();
                    dk.MANV = dr["MANV"].ToString();
                    dk.NGAYDI = DateTime.Parse(dr["NGAYDI"].ToString());
                    dk.MACHUYEN = dr["MACHUYEN"].ToString();
                    dk.SOHIEU = dr["SOHIEU"].ToString();              
                    list.Add(dk);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion
        #region SelectAllDangKyByNgayDi
        public static List<DangKyDTO> SelectALLDangKyByNgayDi(DateTime NgayDi)
        {
                List<DangKyDTO> ds = new List<DangKyDTO>();
                SqlParameter para = new SqlParameter("@ngayDi", System.Data.SqlDbType.DateTime);
                para.Value = NgayDi;
                
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;

                SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted, "T2");
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT * FROM DANGKY WHERE NGAYDI = @ngayDi";
                cmd.Parameters.Add(para);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    DangKyDTO dk = new DangKyDTO();
                    dk.MANV = dr.GetString(0);
                    dk.NGAYDI = dr.GetDateTime(1);
                    dk.MACHUYEN = dr.GetString(2);
                    dk.SOHIEU = dr.GetString(3);
                    ds.Add(dk);
                }
                dr.Close();
                tran.Commit();
                conn.Close();
                return ds;
        }
        #endregion
        #region ThemDangKy
            public static int DangKyLichDi(String MaNV,DateTime NgayDi,String MaChuyen,String SoHieu)
            {
                try
                {
                    SqlParameter para1 = new SqlParameter("@maNV", SqlDbType.Char);
                    para1.Value = MaNV;
                    SqlParameter para2 = new SqlParameter("@ngayDi", SqlDbType.DateTime);
                    para2.Value = NgayDi;
                    SqlParameter para3 = new SqlParameter("@maChuyen", SqlDbType.Char);
                    para3.Value = MaChuyen;
                    SqlParameter para4 = new SqlParameter("@soHieu", SqlDbType.Char);
                    para4.Value = SoHieu;


                    SqlConnection conn = DataProvider.ConnectDB();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.Connection = conn;

                    SqlTransaction tran = conn.BeginTransaction("T1");
                    cmd.Transaction = tran;
                    cmd.CommandText = "INSERT INTO DANGKY VALUES(@maNV,@ngayDi,@maChuyen,@soHieu)";
                    cmd.Parameters.Add(para1);
                    cmd.Parameters.Add(para2);
                    cmd.Parameters.Add(para3);
                    cmd.Parameters.Add(para4);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();

                    cmd.CommandText = "SELECT * FROM NhanVien WHERE MANV = @maNV AND LOAINV = '1'";
                    cmd.Parameters.Add(para1);
                    SqlDataReader dr = cmd.ExecuteReader();
                    System.Threading.Thread.Sleep(5000);
                    if (dr.HasRows)//= exists trog SQL
                    {
                        dr.Close();
                        cmd.Parameters.Clear();

                        cmd.CommandText = "SELECT COUNT(*) FROM DANGKY dk,NHANVIEN nv WHERE dk.MaNV = nv.MaNV AND nv.LoaiNV = '1' AND NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                        cmd.Parameters.Add(para2);
                        cmd.Parameters.Add(para3);
                        cmd.Parameters.Add(para4);
                        object obj1 = cmd.ExecuteScalar();
                        int sotai = obj1 == DBNull.Value ? 0 : Convert.ToInt32(obj1);
                        cmd.Parameters.Clear();

                        cmd.CommandText = "SELECT SOTAI FROM LICHDI WHERE NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                        cmd.Parameters.Add(para2);
                        cmd.Parameters.Add(para3);
                        cmd.Parameters.Add(para4);
                        object obj2 = cmd.ExecuteScalar();
                        int sotaitoida = obj2 == DBNull.Value ? 0 : Convert.ToInt32(obj2);
                        cmd.Parameters.Clear();

                        if (sotai > sotaitoida)
                        {
                            dr.Close();
                            tran.Rollback();
                            return -1;
                            throw new Exception("Đã đủ số tài");
                        }


                    }
                    dr.Close();
                    cmd.Parameters.Clear();

                    cmd.CommandText = "SELECT * FROM NhanVien WHERE MANV = @maNV AND LOAINV = '0'";
                    cmd.Parameters.Add(para1);
                    dr = cmd.ExecuteReader();

                    if (dr.HasRows)//= exists trog SQL
                    {
                        dr.Close();
                        cmd.Parameters.Clear();

                        cmd.CommandText = "SELECT COUNT(*) FROM DANGKY dk,NHANVIEN nv WHERE dk.MaNV = nv.MaNV AND nv.LoaiNV = '0' AND NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                        cmd.Parameters.Add(para2);
                        cmd.Parameters.Add(para3);
                        cmd.Parameters.Add(para4);
                        object obj1 = cmd.ExecuteScalar();
                        int solo = obj1 == DBNull.Value ? 0 : Convert.ToInt32(obj1);
                        cmd.Parameters.Clear();

                        cmd.CommandText = "SELECT SOTAI FROM LICHDI WHERE NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu";
                        cmd.Parameters.Add(para2);
                        cmd.Parameters.Add(para3);
                        cmd.Parameters.Add(para4);
                        object obj2 = cmd.ExecuteScalar();
                        int solotoida = obj1 == DBNull.Value ? 0 : Convert.ToInt32(obj2);
                        cmd.Parameters.Clear();

                        if (solo > solotoida)
                        {
                            dr.Close();
                            tran.Rollback();
                            return -2;
                            throw new Exception("Đã đủ số lơ");
                        }
                    }
                    dr.Close();
                    cmd.Parameters.Clear();
                    tran.Commit();
                    conn.Close();
                    return 1;
                }
                catch (Exception ex)
                {
                    return 0;
                }
            }
        #endregion
        #region XoaDangKy
            public static int XoaDangKy(String MaNV, DateTime NgayDi, String MaChuyen)
            {
                if (DateTime.Compare(NgayDi,DateTime.Now) > 7)
                    return 0;
                try
                {
                    /*Tao Cac Parameter*/
                    SqlParameter para1 = new SqlParameter("@maNV", SqlDbType.Char);
                    para1.Value = MaNV;
                    SqlParameter para2 = new SqlParameter("@ngayDi", SqlDbType.DateTime);
                    para2.Value = NgayDi;
                    SqlParameter para3 = new SqlParameter("@maChuyen", SqlDbType.Char);
                    para3.Value = MaChuyen;
     
                    /*Ket Noi CSDL*/
                    SqlConnection conn = DataProvider.ConnectDB();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.Connection = conn;

                    /*Tao Transaction*/
                    SqlTransaction tran = conn.BeginTransaction("T1");
                    cmd.Transaction = tran;
                    cmd.CommandText = "DELETE FROM DANGKY WHERE MaNV = @maNV AND NgayDi = @ngayDi AND MaChuyen = @maChuyen";
                    cmd.Parameters.Add(para1);
                    cmd.Parameters.Add(para2);
                    cmd.Parameters.Add(para3);
                    cmd.ExecuteNonQuery();
                    
                    tran.Commit();
                    conn.Close();
                    return 1;
                }
                catch (Exception ex)
                {
                    return 0;
                }
            }
        #endregion XoaDangKy
    }
}
